/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.connections;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.http.HttpSession;
import modelo.Usuario;

/**
 *
 * @author educacionit
 */
public class ConnectionMysql {

    private String dbconnection = "com.mysql.jdbc.Driver";
    private String dbString = "jdbc:mysql://localhost/javaweb";
    private String user = "root";
    private String pass = "";

    private void validaSiExisteElUsuario(String usuario) throws ExisteUsuarioSQLException {
        //valido si existe el usuario
        Usuario userObj;
        String consulta = "select * from usuarios where usuario = '" + usuario + "'";

        try {
            Class.forName(dbconnection).newInstance();
            Connection cc = DriverManager.getConnection(dbString, user, pass);
            Statement st = cc.createStatement();
            ResultSet rs = st.executeQuery(consulta);

            while (rs.next()) {
                st.close();
                cc.close();
                throw new ExisteUsuarioSQLException("Existe el Usuario " + usuario);
            }

            st.close();
            cc.close();


        } catch (InstantiationException ie) {
            System.err.println(ie.getMessage().toString());
        } catch (IllegalAccessException ee) {
            System.err.println(ee.getMessage().toString());
        } catch (SQLException ex) {
            System.err.println(ex.getMessage().toString());
        } catch (ClassNotFoundException ex) {

            Logger.getLogger(ConnectionMysql.class.getName()).log(Level.SEVERE, null, ex);
        }/* finally {
         st.close();
         cc.close();
         }*/

    }

    public void insertarUsuario(String usuario, String contrasena) throws ExisteUsuarioSQLException {
        try {

            validaSiExisteElUsuario(usuario);

            String insert = "insert into usuarios (usuario, contrasena, admin) values('" + usuario + "','" + contrasena + "', 0)";

            Class.forName(dbconnection).newInstance();
            Connection cc = DriverManager.getConnection(dbString, user, pass);


            Statement st = cc.createStatement();
            Boolean resultado = st.execute(insert);
            
            st.close();
            cc.close();

        } catch (InstantiationException ie) {
            System.err.println(ie.getMessage().toString());
        } catch (IllegalAccessException ee) {
            System.err.println(ee.getMessage().toString());
        } catch (SQLException ex) {
            System.err.println(ex.getMessage().toString());
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(ConnectionMysql.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    public List<Usuario> ConnectToMysql() {

        List<Usuario> listU = new ArrayList<Usuario>();
        Usuario userObj;

        String consulta = "select * from usuarios";
        try {
            Class.forName(dbconnection).newInstance();
            Connection cc = DriverManager.getConnection(dbString, user, pass);
            Statement st = cc.createStatement();
            ResultSet rs = st.executeQuery(consulta);

            while (rs.next()) {
                userObj = new Usuario();
                userObj.setId(rs.getInt("id"));
                userObj.setUsuario(rs.getString("usuario"));
                userObj.setContrasena(rs.getString("contrasena"));
                userObj.setAdministrador(rs.getInt("admin"));
                listU.add(userObj);
            }

            st.close();
            cc.close();
        } catch (InstantiationException ie) {
            System.err.println(ie.getMessage().toString());
        } catch (IllegalAccessException ee) {
            System.err.println(ee.getMessage().toString());
        } catch (SQLException ex) {
            System.err.println(ex.getMessage().toString());
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(ConnectionMysql.class.getName()).log(Level.SEVERE, null, ex);
        }


        return listU;



    }
}
